<?php
    class StudentQueryCriteria{
        var $grade;
        var $major_id;
        var $class;
        var $gender;
        var $search_id;
        var $search_name;
    }
    class StudentDormitoryAllocatingDAL{
        //为学生分配宿舍
        public function allocateStudentDorm($allocateList){
            $result=false;
            $query_insert="insert into tb_student_dormitory_roster(student_id,apartment_id,floor,dormitory_id,add_time,status) 
            values(:student_id,:apartment_id,:floor,:dormitory_id,now(),1);";
            $query_update="update tb_student_dormitory_roster set apartment_id=:apartment_id,
            floor=:floor,dormitory_id=:dormitory_id,add_time=now(),status=1 where student_id=:student_id;";

            $pdo=myPDO::getInstance();
            try{
                $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $pdo->beginTransaction();

                foreach($allocateList as $allocate){
                    switch($allocate->action){
                        case 1:
                            $statement=$pdo->prepare($query_insert,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                        break;
                        case 2:
                            $statement=$pdo->prepare($query_update,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                        break;
                        default:
                            $statement=$pdo->prepare($query_insert,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                        break;
                    }
                    $statement->bindValue(':student_id',$allocate->student_id);
                    $statement->bindValue(':apartment_id',$allocate->apartment_id);
                    $statement->bindValue(':floor',$allocate->floor);
                    $statement->bindValue(':dormitory_id',$allocate->dormitory_id);
                    $result=$statement->execute();
                }
                $pdo->commit();  
            }catch(Exception $e){
                $pdo->rollBack();
                //echo "Failed: ".$e->getMessage();
            }
            
            $statement->closeCursor();

            return $result;
        }

        //判断学生是否已分配宿舍
        public function isStudentDormitory($student_id){
            $query="select status from tb_student_dormitory_roster where student_id=:student_id";
            
            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':student_id',$student_id);
            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
            }
            $statement->closeCursor();

            return $result;
        }

        //移除学生宿舍
        public function removeStudentDormitory($removeList){
            $query="update tb_student_dormitory_roster set status=0 where student_id=:student_id;";
            $query_del="";
            $pdo=myPDO::getInstance();
            try{
                $pdo->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
                $pdo->beginTransaction();

                $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
                foreach($removeList as $remove){
                    $statement->bindValue(':student_id',$remove->student_id);
                    $result=$statement->execute();
                }

                $pdo->commit();  
            }catch(Exception $e){
                $pdo->rollBack();
                //echo "Failed: ".$e->getMessage();
            }
            $statement->closeCursor();

            return $result;
        }

        //获取学生列表
        public function getStudentList($allocate,$grade,$major,$class,$gender,$search,$page,$pageSize){
            $result=null;
            $query_allocate='';
            switch((int)$allocate){
                case 0:
                    $query_allocate="";
                    break;
                case 1:
                    $query_allocate=" and (tb_student_info_list.student_id in (select `student_id` from tb_student_dormitory_roster  where status=1)) ";
                    break;
                case 2:
                    $query_allocate=" and (tb_student_info_list.student_id not in (select `student_id` from tb_student_dormitory_roster where status=1)) ";
                    break;
                default:break;
            }
            $query_page="select FOUND_ROWS() as count;";
            $query_all="select SQL_CALC_FOUND_ROWS tb_student_info_list.student_id,name,gender,grade,tb_major_list.major_id,tb_major_list.major_name,class 
            from tb_student_info_list,tb_major_list 
            where tb_student_info_list.status=1 
            and tb_student_info_list.major_id=tb_major_list.major_id 
            and grade=:grade 
            and tb_student_info_list.major_id=:major_id 
            and class=:class ".($gender==0?'':' and gender=:gender ').
            ($search==''?'':'and (tb_student_info_list.student_id=:search_id or tb_student_info_list.name=:search_name) ').$query_allocate.
            " order by tb_student_info_list.student_id LIMIT :page,:pageSize;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query_all,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':grade',$grade);
            $statement->bindValue(':major_id',$major);
            $statement->bindValue(':class',$class);
            if(!$gender==0)$statement->bindValue(':gender',$gender);
            if(!$search==''){
                $statement->bindValue(':search_id',$search);
                $statement->bindValue(':search_name',$search);
            }

            $statement->bindValue(":page",$page,PDO::PARAM_INT);
            $statement->bindValue(":pageSize",$pageSize,PDO::PARAM_INT);
            if($statement->execute()){
                $studentList=$statement->fetchALL(PDO::FETCH_ASSOC);
                $row=$pdo->query($query_page);
                $count=$row->fetch(PDO::FETCH_ASSOC);
                $row->closeCursor();

                $result=array(
                    'studentList'=>$studentList,
                    'count'=>$count['count']
                );
            }
            $statement->closeCursor();
            
            if(!$studentList==null){
                
            }

            return $result;
        }

        //获取辅导员所管理的宿舍列表
        public function getDormitoryList($instructor,$grade,$major,$gender){
            $result=null;
            $query="select tb_apartment_info.apartment_id,tb_apartment_info.apartment_name,
            tb_dormitory_info.floor,tb_dormitory_info.dormitory_id,tb_dormitory_info.property,
            tb_instructor_dormitory_allocating_list.beds 
            from tb_instructor_dormitory_allocating_list,tb_apartment_info,tb_dormitory_info 
            where tb_apartment_info.apartment_id=tb_instructor_dormitory_allocating_list.apartment_id 
            and tb_instructor_dormitory_allocating_list.status=1 
            and tb_dormitory_info.apartment_id=tb_instructor_dormitory_allocating_list.apartment_id 
            and tb_dormitory_info.floor=tb_instructor_dormitory_allocating_list.floor 
            and tb_dormitory_info.dormitory_id=tb_instructor_dormitory_allocating_list.dormitory_id 
            and uid=:uid 
            and grade=:grade 
            and major_id=:major_id".($gender==0?'':' and tb_dormitory_info.property=:gender').";";
            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':uid',$instructor);
            $statement->bindValue(':grade',$grade);
            $statement->bindValue(':major_id',$major);
            $gender==0?:$statement->bindValue(':gender',$gender);

            if($statement->execute()){
                $result=$statement->fetchALL(PDO::FETCH_ASSOC);
            }
            $statement->closeCursor();
            return $result;
        }

        //获取宿舍已分配床位数量
        public function getAllocatedBedsNumber($apartmentID,$dormitoryID,$grade,$major){
            $query="select count(1) as count 
            from tb_student_dormitory_roster,tb_student_info_list 
            where tb_student_info_list.student_id=tb_student_dormitory_roster.student_id 
            and tb_student_info_list.grade=:grade 
            and tb_student_info_list.major_id=:major_id 
            and apartment_id=:apartment_id and dormitory_id=:dormitory_id 
            and tb_student_dormitory_roster.status=1 
            and tb_student_info_list.status=1;";

            $pdo=myPDO::getInstance();
            $statement=$pdo->prepare($query,array(PDO::ATTR_CURSOR =>PDO::CURSOR_FWDONLY));
            $statement->bindValue(':apartment_id',$apartmentID);
            $statement->bindValue(':dormitory_id',$dormitoryID);
            $statement->bindValue(':grade',$grade);
            $statement->bindValue(':major_id',$major);
            if($statement->execute()){
                $result=$statement->fetch(PDO::FETCH_ASSOC);
            }
            $statement->closeCursor();
            return $result;
        }
    }
?>